package com.javasm.dao.impl;

import com.javasm.dao.BookDao;
import com.javasm.entity.Book;
import com.javasm.entity.BookSecondSort;
import com.javasm.entity.BookTopSort;
import com.javasm.entity.sql.BookManageSql;
import com.javasm.util.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author WangJie
 * @version 1.0
 * @Description:
 * @date: 2020/7/21 21:19
 * @since JDK 1.8
 */
public class BookDaoImpl implements BookDao {
    /**
     * 分页查询图书列表
     *
     * @param page     当前页码
     * @param pageSize 每页查询条数
     * @return
     */
    @Override
    public List<Book> selectBookByPage(int page, int pageSize, Book book) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Book> bookInfo = new ArrayList<>();
        StringBuilder builder = new StringBuilder(BookManageSql.SELECT_BOOK_BY_PAGE);
        List<Object> params = new ArrayList<>(4);
        if (book.getBid() != null) {
            builder.append("AND sb.bid = ? ");
            params.add(book.getBid());
        }
        if (book.getBook_name() != null && !"".equals(book.getBook_name().trim())) {
            builder.append("AND sb.book_name like ? ");
            params.add("%" + book.getBook_name() + "%");
        }
        if (book.getTsb_id() != 0) {
            builder.append("AND sb.tsb_id = ? ");
            params.add(book.getTsb_id());
        }
        if (book.getSsb_id() != 0) {
            builder.append("AND sb.ssb_id = ? ");
            params.add(book.getSsb_id());
        }
        builder.append("LIMIT ?,?");
        params.add((page - 1) * pageSize);
        params.add(pageSize);
        System.out.println(builder.toString());
        try {
            conn = DruidUtil.getConnection();
            ps = conn.prepareStatement(builder.toString());
            // 占位符赋值
            for (int i = 0; i < params.size(); i++) {
                ps.setObject((i + 1), params.get(i));
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                Book book1 = new Book(rs.getInt("sb.bid"),
                        rs.getString("sb.book_name"),
                        rs.getString("sb.author_name"),
                        rs.getString("sb.coverUrl"),
                        rs.getString("sb.description"),
                        rs.getInt("sb.word_count"),
                        rs.getInt("sb.status"),
                        rs.getInt("sb.attribution"),
                        rs.getInt("sb.tsb_id"),
                        rs.getInt("sb.ssb_id"),
                        rs.getString("stsb.tsb_name"),
                        rs.getString("sssb.ssb_name"));
                bookInfo.add(book1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,ps,rs);
        }
        return bookInfo;
    }


    /**
     * 查询图书信息总记录数
     *
     * @return
     */
    @Override
    public int selectTotalCount(Book book) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuilder builder = new StringBuilder(BookManageSql.SELECT_BOOK_TOTALCOUNT);
        int totalCount = 0;
        List<Object> params = new ArrayList<>(4);
        boolean isWhere = true;
        if (book.getBid() != null) {
            isWhere = false;
            builder.append("WHERE sb.bid = ? ");
            params.add(book.getBid());
        }
        if (book.getBook_name() != null && !"".equals(book.getBook_name().trim())) {
            if (isWhere) builder.append("WHERE ");
            else builder.append("AND ");
            isWhere = false;
            builder.append("sb.book_name like ? ");
            params.add("%" + book.getBook_name() + "%");
        }
        if (book.getTsb_id() != 0) {
            if (isWhere) builder.append("WHERE ");
            else builder.append("AND ");
            isWhere = false;
            builder.append("sb.tsb_id = ? ");
            params.add(book.getTsb_id());
        }
        if (book.getSsb_id() != 0) {
            if (isWhere) builder.append("WHERE ");
            else builder.append("AND ");
            builder.append("sb.ssb_id = ?");
            params.add(book.getSsb_id());
        }
        System.out.println(builder.toString());
        try {
            conn = DruidUtil.getConnection();
            ps = conn.prepareStatement(builder.toString());
            // 占位符赋值
            for (int i = 0; i < params.size(); i++) {
                ps.setObject((i + 1), params.get(i));
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                totalCount = rs.getInt("COUNT(1)");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn, ps, rs);
        }
        return totalCount;
    }

    /**
     * 查询所有图书一级分类
     *
     * @return
     */
    @Override
    public List<BookTopSort> selectBookTopSort() {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        List<BookTopSort> topSortList = null;
        try {
            topSortList = queryRunner.query(BookManageSql.SELECE_BOOK_TOP_SORT, new BeanListHandler<>(BookTopSort.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return topSortList;
    }

    /**
     * 根据一级图书分类id查询二级分类信息
     *
     * @param tsbId 一级分类id
     * @return
     */
    @Override
    public List<BookSecondSort> selectBookSecondSortByTsbId(String tsbId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        List<BookSecondSort> secondSortList = null;
        try {
            secondSortList = queryRunner.query(BookManageSql.SELECE_BOOK_SECOND_SORT_BY_TSBID, new BeanListHandler<>(BookSecondSort.class), tsbId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return secondSortList;
    }

    /**
     * 分页查询图书分类列表
     *
     * @param page     当前页码
     * @param pageSize 每页查询条数
     * @return
     */
    @Override
    public List<Map<String, Object>> selectBookSortByPage(int page, int pageSize) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        List<Map<String, Object>> bookSortList = null;
        try {
            bookSortList = queryRunner.query(BookManageSql.SELECT_BOOK_SORT_BY_PAGE, new MapListHandler(), (page - 1) * pageSize, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return bookSortList;
    }

    /**
     * 查询图书分类信息总记录数
     *
     * @return
     */
    @Override
    public int selectBookSortTotalCount() {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        int totalCount = 0;
        try {
            totalCount = ((Long) queryRunner.query(BookManageSql.SELECT_BOOK_SORT_TOTALCOUNT, new ScalarHandler<>())).intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return totalCount;
    }

    /**
     * 新增图书二级分类
     *
     * @param tsb   一级分类id
     * @param ssb 二级分类名称
     * @return
     */
    @Override
    public int insertBookSecondSort(String tsb, String ssb) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        int result = 0;
        try {
            result = queryRunner.update(BookManageSql.INSERT_BOOK_SECOND_SORT, tsb, ssb);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 新增图书一二级分类
     *
     * @param tsb 一级分类名称
     * @param ssb 二级分类名称
     * @return
     */
    @Override
    public int insertBookTopAndSecondSort(String tsb, String ssb) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;
        try {
            conn = DruidUtil.getConnection();
            conn.setAutoCommit(false); // 关闭自动提交
            ps = conn.prepareStatement(BookManageSql.INSERT_BOOK_TOP_SORT, Statement.RETURN_GENERATED_KEYS); // 返回自增id的值
            ps.setObject(1, tsb);
            ps.executeUpdate(); // 执行新增老师sql
            // 获得自增的id  select last_insert_id()  rs  JDBC已经封装了这个自增id
            rs = ps.getGeneratedKeys();// Long类型的数值
            long tsbId = 0;
            if (rs.next()) {
                tsbId = rs.getLong(1); // 参数为第几列
            }
            // 执行第二条sql
            ps = conn.prepareStatement(BookManageSql.INSERT_BOOK_SECOND_SORT);
                ps.setObject(1,tsbId);
                ps.setObject(2, ssb);
            result = ps.executeUpdate();
            // 提交事务
            conn.commit();

        } catch (SQLException e) {
            e.printStackTrace();
            // 数据回滚
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            DruidUtil.close(conn, ps, rs);
        }
        return result;
    }
}
